
insert overwrite table jms_dm.dm_service_site_out_brand_cooperate_company_agg_dt partition(dt)
-- 百世出入库统计网点品牌合作公司汇总报表
select
    service_site.dt as site_in_date,  --日期
    max(province), --省
    max(city),     -- 城市
    max(area),     -- 区
    expresscompanycode     as expresscompanycode,
    sum(if(detail.isInSiteFlag==1, 1, 0)) as site_in_cnt, --入库量
    sum(if(detail.isInSiteFlag==0 and detail.statuscode==40, 1, 0)) as site_out_cnt, --出库量
    sum(if(detail.isInSiteFlag==0 and detail.statuscode==70, 1, 0)) as site_abnormal_out_cnt, --异常出库量
    sum(if(detail.self_pickup_current_billcode is not null and detail.self_pickup_current_statuscode==40, 1, 0)) as site_current_out_cnt, --当日出库量
    sum(if(detail.self_pickup_current_billcode is not null and detail.self_pickup_current_statuscode in (40,70), 1, 0)) as site_current_end_cnt, --当日完结量
    sum(if(detail.self_pickup_1_billcode is not null and detail.self_pickup_1_statuscode==40, 1, 0)) as site_t1_out_cnt, --T+1日出库量
    sum(if(detail.self_pickup_1_billcode is not null and detail.self_pickup_1_statuscode in (40,70), 1, 0)) as site_t1_end_cnt, --T+1日完结量
    sum(if(detail.self_pickup_3_billcode is not null and detail.self_pickup_3_statuscode==40, 1, 0)) as site_t3_out_cnt, --T+3日出库量
    sum(if(detail.self_pickup_3_billcode is not null and detail.self_pickup_3_statuscode in (40,70), 1, 0)) as site_t3_end_cnt, --T+3日完结量
    sum(if(detail.self_pickup_6_billcode is not null and detail.self_pickup_6_statuscode==40, 1, 0)) as site_t6_out_cnt, --T+6日出库量
    sum(if(detail.self_pickup_6_billcode is not null and detail.self_pickup_6_statuscode in (40,70), 1, 0)) as site_t6_end_cnt, --T+6日完结量
    agency_code,
    max(agency_name),
    province_code,
    city_code,
    area_code,
       detail.servicesitecode,
       max(detail.servicesitename) as servicesitename,
       nvl(detail.cooperate_company,'') as cooperate_company ,
    service_site.dt
from
(
    select servicesitecode,dt
    from
    (
    select servicesitecode, count(1) as cnt, dt
    from jms_dm.dm_service_site_out_statistics_dt
     where dt  >=date_sub('{{ execution_date | cst_ds }}',6)
  and dt <='{{ execution_date | cst_ds }}'
      and isInSiteFlag=1
    group by servicesitecode,dt
    ) tmp where cnt>0
) service_site
left join
(
select     billcode,
    case when expresscompanycode in('JTKD','ZTO','YUNDA','YTO','STO','ZGYZ','BESTEXP','EMS','FWEXP','SFEXP','JD','ZMKM') then expresscompanycode else 'OTHERS' end as expresscompanycode,
    province,city,area,
       servicesitecode,
       servicesitename
       servicesitename,statuscode,
    self_pickup_current_billcode,
    self_pickup_current_statuscode,
    self_pickup_1_billcode,
    self_pickup_1_statuscode,
    self_pickup_3_billcode,
    self_pickup_3_statuscode,
    self_pickup_6_billcode,
    self_pickup_6_statuscode,
    isInSiteFlag,
    agency_code ,
    agency_name ,
    province_code ,
    city_code ,
    area_code ,
    cooperate_company,
    dt
from jms_dm.dm_service_site_out_statistics_dt
where dt  >=date_sub('{{ execution_date | cst_ds }}',6)
  and dt <='{{ execution_date | cst_ds }}'
) detail
    on detail.servicesitecode=service_site.servicesitecode
           and detail.dt=service_site.dt
where detail.servicesitecode is not null
group by agency_code,province_code,city_code,area_code,service_site.dt,expresscompanycode,detail.servicesitecode,detail.cooperate_company
distribute by 10
;